let db = require('../db/index')

// 公共常量
const gs = {
	success: {
		code: 200,
		msg: "请求成功",
		data: []
	},
	sct: "0d56ac727fbb19afda38f923d9778040", //小程序
	appId: "wxfea5e03656cf9bac",
}
/**
*返回36位uuid
*位数 8-4-4-4-12
*格式 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
 */
const getUuid = () => {
	var s = [];
	var hexDigits = "0123456789abcedef";
	for (var i = 0; i < 36; i++) {
		s[i] = hexDigits.substr(Math.floor(Math.random() * 0x10), 1);
	}
	s[14] = "4";
	s[19] = hexDigits.substr((s[19] & 0x3) | 0x8, 1);
	s[8] = s[13] = s[18] = s[23] = "-";
	var uuid = s.join("");
	return uuid;
}


// 获取年-月-日 时：分：秒
const currentDate = () => {
	let now = new Date();

	// 月份是从0开始的，所以需要+1  
	let month = String(now.getMonth() + 1).padStart(2, '0');
	// 日期和小时、分钟、秒直接获取  
	let day = String(now.getDate()).padStart(2, '0');
	let hour = String(now.getHours()).padStart(2, '0');
	let minute = String(now.getMinutes()).padStart(2, '0');
	let second = String(now.getSeconds()).padStart(2, '0');

	// 使用模板字符串来组合日期和时间  
	return `${now.getFullYear()}-${month}-${day} ${hour}:${minute}:${second}`;
}

// 新增
const insertSqlPublic = async (tableName, databaseName, params) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			let sql =
				`SELECT column_name AS col FROM information_schema.columns WHERE table_name='${tableName}' AND table_schema='${databaseName}' AND COLUMN_NAME != 'id'`;

			// 执行 SQL 语句
			let [columns] = await connection.query(sql);
			let str = `INSERT INTO ${tableName} (`;
			let astr = '';

			// 过滤不需要的列
			for (var j = 0; j < columns.length; j++) {
				if (!params.hasOwnProperty(columns[j]["col"])) {
					columns.splice(j, 1)
					j--;
					continue;
				}
			}
			for (var i = 0; i < columns.length; i++) {
				if (columns.length - 1 == i) {
					if (params.hasOwnProperty('id')) {
						str += columns[i]["col"] + ",ent_date,mod_date,id) values ( ";
						//astr += `, '${params.id}'`;
					} else {
						str += columns[i]["col"] + ",ent_date,mod_date) values ( ";
					}
				} else {
					str += columns[i]["col"] + ",";
				}
				for (var key in params) {
					if (key != columns[i]["col"]) {
						continue;
					}
					astr += "'" + params[key] + "',"
				}
			}
			if (params.hasOwnProperty('id')) {
				str += astr + "'" + currentDate() + "','" + currentDate() + "', '" + params.id + "')";
			} else {
				str += astr + "'" + currentDate() + "','" + currentDate() + "')";
			}
			// 返回构建好的 SQL 语句
			return str;
		} catch (e) {
			console.error('Query error:', e);
			throw new Error(e.message);
		} finally {
			// 无论是否成功，都释放连接
			connection.release();
		}
	} catch (err) {
		console.error('Database connection error:', err);
		throw new Error('数据库连接错误');
	}
};

// 分页获取

const getMysqlPage = async (sql, fields, params) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			// console.log(params, 'params')
			let currentPage = params["currentPage"] || 1;
			let pageSize = params['pageSize'] || 10;
			if (pageSize == -1) {//全部数据
				const [rows] = await connection.execute(sql, fields);
				let allData = {};
				allData.rows = rows;
				allData.total = rows.length;
				return allData;
			} else {
				let page = 1;
				let pageitemcount = 10; //每一页显示数量 
				let itemcount = -1;

				if (currentPage)
					page = parseInt(currentPage);
				if (pageSize)
					pageitemcount = parseInt(pageSize);

				const countsql = "select count(*) as ITEMSCNT from (" + sql + ") a";
				const [rows] = await connection.execute(countsql, fields);
				itemcount = parseInt(rows[0]["ITEMSCNT"]);

				const begin = (page - 1) * pageitemcount + "";
				const end = pageitemcount + "";

				const pagesql = " SELECT *  FROM (" + sql + ") a limit " + begin + "," + end;
				let result = {};
				result.total = itemcount;
				// result.sql = pagesql;
				let [resultRows] = await connection.execute(pagesql, fields);
				result.rows = resultRows
				return result;
			}
		} catch (err) {
			console.error('Query error:', err);
			throw new Error(err.message);
		} finally {
			// 无论查询是否成功，都释放连接
			connection.release();
		}

	} catch (error) {
		console.error('Database connection error:', error);
		throw new Error('数据库连接错误');
	}

}

module.exports = {
	gs,
	getUuid,
	currentDate,
	getMysqlPage,
	insertSqlPublic
}